//
//  ZSY_DataBaseManager.m
//  ZSY_BreadTrip
//
//  Created by 张诗宇 on 16/1/11.
//  Copyright © 2016年 张诗宇. All rights reserved.
//

#import "ZSY_DataBaseManager.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

@interface ZSY_DataBaseManager ()

@property (nonatomic,retain)FMDatabase *fmdb;

@end


@implementation ZSY_DataBaseManager

+ (instancetype)shareDataBase{
    static ZSY_DataBaseManager *data = nil;
    if (data == nil) {
        data = [[ZSY_DataBaseManager alloc]init];
    }
    return data;
}

// 打开数据库
- (void)openDB{
    
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *filePath = [documentPath stringByAppendingPathComponent:@"City.sqlite"];
        NSLog(@"%@", filePath);
    // 创建数据库对象
    self.fmdb = [[FMDatabase alloc]initWithPath:filePath];
    // 打开数据库
    BOOL result = [self.fmdb open];
    if (result) {
        NSLog(@"打开数据库成功");
    }else{
        NSLog(@"打开数据库失败");
    }
}
// 创建表
-(void)createCityTable{
    NSString *sql =@"CREATE TABLE IF NOT EXISTS Travel(number INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT, product_id TEXT, title_page TEXT)";
    BOOL result = [self.fmdb executeStatements:sql];
    if (result) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    
}
// 插入数据
- (void)insertCity:(ZSY_CityModel *)city
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Travel(title, product_id, title_page) VALUES('%@','%@','%@')",city.title,city.product_id,city.title_page];
    BOOL result = [self.fmdb executeStatements:sql];
    if (result) {
                NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

// 查询
-(NSMutableArray *)selectCity{
    
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT *FROM Travel";
    // 执行查询语句
    FMResultSet *resultset = [self.fmdb executeQuery:sql];
    //    FMResultSet 实例是用来遍历表中符合要求的每一行
    
    BOOL result = [self.fmdb executeStatements:sql];
    if (result) {
        NSLog(@"查询成功");
    }else{
        NSLog(@"查询失败");
    }
    
    while ([resultset next]) {
        
        NSString *title = [resultset stringForColumn:@"title"];
        NSString *product_id = [resultset stringForColumn:@"product_id"];
        NSString *title_page = [resultset stringForColumn:@"title_page"];
        ZSY_CityModel *model = [[ZSY_CityModel alloc]init];
        model.title = title;
        model.product_id = product_id;
        model.title_page = title_page;
        [array addObject:model];
        
    }
    return array;
    
    
    
}


// 删除
- (void)deleteCityTable:(ZSY_CityModel *)model{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM Travel WHERE title = '%@'", model.title];
    
    BOOL result = [self.fmdb executeStatements:sql];
    if (result) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}

// 查询
- (NSMutableArray *)selectCity:(ZSY_CityModel *)model{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Travel WHERE title = '%@'", model.title];
    // 执行查询语句
    FMResultSet *resultset = [self.fmdb executeQuery:sql];
    //    FMResultSet 实例是用来遍历表中符合要求的每一行
    while ([resultset next]) {
        // 取出表中字段为number的值
        //        NSInteger number = [resultset intForColumn:@"number"];
        NSString *title = [resultset stringForColumn:@"title"];
        //        NSString *product_id = [resultset stringForColumn:@"product_id"];
        ZSY_CityModel *model = [[ZSY_CityModel alloc]init];
        model.title = title;
        //        model.product_id = product_id;
        [array addObject:model];
    }
    return array;
}



@end
